﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using CommunitySystemModel;
using System.Data;

namespace CommunitySystem.BO
{
   public static class ZJZZRBO
    {
       public static ObservableCollection<ZJZZRModel> GetZJZZRModel(string con, int index, int pagesize)
        {
            ObservableCollection<ZJZZRModel> rights = new ObservableCollection<ZJZZRModel>();
            string sql = "";
            sql = string.Format(@"select top({0}) ROW_NUMBER() over(order by ZRDWID,CreateDate desc) as Sequence,IsSelected='false'
                                ,* from
                                (
                                select ROW_NUMBER() over(order by ID desc) as rowsID,* from ZJZZR emp where 1=1 
                                ) a where a.rowsID>=
                                (
                                SELECT ISNULL(MAX(rowsID),0) FROM 
                                (
                                select top({0}*({1}-1)+1) * from
                                (
                                select ROW_NUMBER() over(order by ID desc) as rowsID,* from ZJZZR emp where 1=1 
                                )b
                                )c
                                ) order by ZRDWID,CreateDate desc", pagesize, index, con);

            if (!string.IsNullOrEmpty(con))
            {
                string sqlorgids = AccessHelper.GetInstance().ExcuteDataSet(string.Format(" select OrgCode from UserInfo where LoginName='{0}'", con)).Tables[0].Rows[0]["OrgCode"].ToString();
                sql = string.Format(@"--declare @ids varchar
                        --select @ids=OrgCode from UserInfo where LoginName='{2}'
                                select top({0}) ROW_NUMBER() over(order by ZRDWID,CreateDate desc) as Sequence,IsSelected='false'
                                ,* from
                                (
                                select ROW_NUMBER() over(order by ID desc) as rowsID,* from ZJZZR emp where 1=1 and zrdwid in ({2}) 
                                ) a where a.rowsID>=
                                (
                                SELECT ISNULL(MAX(rowsID),0) FROM 
                                (
                                select top({0}*({1}-1)+1) * from
                                (
                                select ROW_NUMBER() over(order by ID desc) as rowsID,* from ZJZZR emp where 1=1 and zrdwid in ({2}) 
                                )b
                                )c
                                ) order by ZRDWID,CreateDate desc
                            ", pagesize, index, sqlorgids);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                ZJZZRModel khm = new ZJZZRModel();
                khm = ZJZZRModel.FillEntity(dr);

                rights.Add(khm);
            }
            return rights;
        }
       public static int GetZJZZRModelCount(string con)
        {
            string sql = string.Format(@"select  COUNT(id) from ZJZZR emp where 1=1 {0} ", con);
            if(!string.IsNullOrEmpty(con))
                sql = string.Format(@"declare @ids varchar
                        select @ids=OrgCode from UserInfo where LoginName='{0}'
                        select  COUNT(id) from ZJZZR emp where 1=1 and zrdwid in (@ids) ", con);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
        }

       public static bool AddZJZZR(ZJZZRModel model)
       {
           string sql = string.Format(@"INSERT INTO ZJZZR
           ([ZCDW] ,[ZCDWID],[ZRDW],[ZRDWID],[ZRRQ],[WH],[BZ],[CreateUser],[CreateDate])
     VALUES
           ((select Name from OrgForHR where ID={0}),{0} ,(select Name from OrgForHR where ID={1}),{1} ,'{2}'  ,'{3}','{4}','{5}','{6}')"
               , model.ZCDWID,model.ZRDWID,model.ZRRQ,model.WH,model.BZ,model.CreateUser,model.CreateDate);
           if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0) return true;
           else return false;
       }

     
       public static bool AddZJZZR_EMP(List<ZJZZR_Emp> models)
       {
           string sql = string.Empty;
           foreach (var item in models)
           {
               sql += string.Format(@"INSERT INTO ZJZZR_Emp
           ([ZJZZRId],[YDWID],[YDW],[YKS],[YZW],[ZRDWID],[ZRDW],[XKS],[XZW],[ZWCC],[BZ],[CreateUser],[CreateDate],XM,EId,ZWSX)
     VALUES
           ({0},{1},'{2}','{3}','{4}' ,{5},'{6}' ,'{7}','{8}','{9}' ,'{10}','{11}','{12}','{13}',{14},'{15}');"
             , item.ZJZZRId, item.YDWID, item.YDW, item.YKS, item.YZW, item.ZRDWID, item.ZRDW, item.XKS, item.XZW, item.ZWCC, item.BZ, item.CreateUser, item.CreateDate, item.XM, item.EId,item.ZWSX);
           }
           //添加人员的后设置整建制转任备注为人员未转任
           sql += string.Format(@"update ZJZZR set BZ='{0}' where ID={1}", "人员未转任", models.FirstOrDefault().ZJZZRId);
           if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0) return true;
           else return false;
       }
 

       public static ObservableCollection<ZJZZR_Emp> GetZJZZR_Emp(string con, int index, int pagesize)
       {
           ObservableCollection<ZJZZR_Emp> rights = new ObservableCollection<ZJZZR_Emp>();
           string sql = "";
           sql = string.Format(@"select  ROW_NUMBER() over(order by CreateDate,YDWID desc) as Sequence,IsSelected='false'
                                ,* from ZJZZR_Emp where 1=1 {0} order by CreateDate desc", con);


           DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               ZJZZR_Emp khm = new ZJZZR_Emp();
               khm = ZJZZR_Emp.FillEntity(dr);

               rights.Add(khm);
           }
           return rights;
       }

       public static bool SBZJZZR_EMP(ZJZZR_Emp model)
       {
           string sql = string.Empty;
           sql += string.Format(@"UPDATE ZJZZR_Emp
   SET [XKS] ='{0}',[XZW] = '{1}',[ZWSX] ='{2}',[BZ] = '{3}',[CreateUser] = '{4}' ,[CreateDate] ='{5}'
      ,[FGCSGZ] = '{6}',[BegRQ] = '{7}',[EndRQ] = '{8}',[TLSJ] = '{9}' WHERE id={10};"
         , model.XKS, model.XZW, model.ZWSX, model.BZ, model.CreateUser, model.CreateDate, model.FGCSGZ, model.BegRQ, model.EndRQ, model.TLSJ, model.Id);
           if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0)
           {
               //人员信息自动从原单位跳至新单位  科室新  进入本单位时间
               string upsql = string.Format("update Emp_BasicInfo set OrgID={0},PositionName='{1}',NowWorkDate=(select ZRRQ from ZJZZR where ID={3}) where ID={2}", model.ZRDWID, model.XKS, model.EId, model.ZJZZRId);
               AccessHelper.GetInstance().ExecuteNonQuery(upsql);
               //职务信息：
               #region
                //职务名称 DutyNo：提取转任框中的职务名称     
                //科室名称DutyName：提取转任框中的科室名称
                //职务层次 DutyLevel：提取转任框中的职务层次
                //职务属性 DutyAttribute：提取转任框中的职务属性，如框内无修改原职务属性不变
                //批准时间 OKDate：提取转任框中的自 时间
                //任职机构名称OrganizationName：提取新单位名称
                //任期：空着（此处后面需求会对此项进行修改）
                //任职试用期：无需更新（直接转任没有试用期，竞争上岗有试用期，但此处已更新）
                //分管从事工作 WorkCharge：提取转任框中的内容
                //集体排序：空着 
                //是否破格IsExceptionalPromotion：否
                //任职方式ServiceMode：职务属性为领导职务的，显示转任领导职务；职务属性为非领导职务的，显示转任非领导职务
                //任现职务层次时间 JobDate：提取上一条在任记录时间????
                //任现职务属性时间 RXRZWSXSJ：原在任为 领导职务，现最新在任为非领导职务的，提取职务信息记录中同层次同属性???记录的任现职务属性时间 
                //如果没有相同记录，提取转任框中自 时间
                //原在任为领导职务，现为领导职务，提取上一条在任的职务属性时间
                //原在任为非领导职务，现为非领导职务，提取上一条在任的职务属性时间
                //任职状态 JobStatus：在任 （将上一条在任改为不在任）

               #endregion
               //在任变不在任
               upsql = string.Format(@"update Emp_Duty set JobStatus='不在任' where EmpbasicID={0}",model.EId);
               AccessHelper.GetInstance().ExecuteNonQuery(upsql);
               //插入职务信息
               upsql = string.Format(@"declare @YZWSX varchar(100),@XZWSX varchar(100),@YZWCC varchar(100),@XZWCC varchar(100),@zwsxsj datetime
                        select top 1 @YZWSX=DutyAttribute from Emp_Duty where JobStatus='不在任' and EmpbasicID={0} order by OKDate desc
                        select @XZWSX=zwsx,@XZWCC=ZWCC from ZJZZR_Emp where ID={1}
                        if(@YZWSX='领导职务' and @XZWSX='非领导职务')
                        begin
                        if exists(select top 1 RXRZWSXSJ from Emp_Duty where EmpbasicID={0} and DutyLevel=@XZWCC and DutyAttribute=@XZWSX  order by OKDate desc)
                        select top 1 @zwsxsj=RXRZWSXSJ from Emp_Duty where EmpbasicID={0} and DutyLevel=@XZWCC and DutyAttribute=@XZWSX  order by OKDate desc
                        else
                        select @zwsxsj=BegRQ from ZJZZR_Emp where ID={1}
                        end
                        else if(@YZWSX='领导职务' and @XZWSX='领导职务')
                        select top 1 @zwsxsj=RXRZWSXSJ from Emp_Duty where JobStatus='不在任' and EmpbasicID={0} order by OKDate desc
                        else if(@YZWSX='非领导职务' and @XZWSX='非领导职务')
                        select top 1 @zwsxsj=RXRZWSXSJ from Emp_Duty where JobStatus='不在任' and EmpbasicID={0} order by OKDate desc

                        insert into Emp_Duty (EmpbasicID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,OrganizationName,WorkCharge,IsExceptionalPromotion,ServiceMode
                        ,JobDate,RXRZWSXSJ,JobStatus)
                        select eid,XZW,XKS,ZWCC,zwsx ,begrq,ZRDW,fgcsgz,'否',case when ZWSX='领导职务' then '转任领导职务' else '转任非领导职务' end
                        ,(select top 1 JobDate from Emp_Duty where JobStatus='不在任' and EmpbasicID=EId order by OKDate desc)
                        ,@zwsxsj,'在任'
                         from ZJZZR_Emp where id={1} and bz='已转任'", model.EId, model.Id);
               AccessHelper.GetInstance().ExecuteNonQuery(upsql);
               //简历信息
               //变简历上条结束日期
               upsql = string.Format(@" update Emp_Resume set EndDate='{1}'  where EmpbasicID={0} and (EndDate is null or EndDate ='')",model.EId,model.BegRQ);
               AccessHelper.GetInstance().ExecuteNonQuery(upsql);
               //插入新的简历
               upsql = string.Format(@"INSERT INTO Emp_Resume
                       ([EmpbasicID],[BeginDate],[EndDate],[Unit],[Duty],[IsPeriod])
                        select eid,BegRQ,null,ZRDW,(XKS+XZW),0 from ZJZZR_Emp where id={0}",model.Id);
               AccessHelper.GetInstance().ExecuteNonQuery(upsql);
               return true;
           }
           else return false;
       }

       public static bool QRZR(string con)
       {
           string sql = string.Format(@"update ZJZZR set BZ='人员已转任' where id in ({0})",con);
           if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0) return true;
           else return false;
       }
    }
}
